package cn.lxycx.dataterrace.interceptor.validates;

import cn.lxycx.dataterrace.interceptor.AbsValidateInterceptor;
import cn.lxycx.dataterrace.util.FormValidate;
import cn.lxycx.dataterrace.util.ServletPro;
import com.jfinal.kit.PropKit;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import lxycx.util.validate.Validate.Ret;

import org.apache.log4j.Logger;

import com.jfinal.core.Controller;
import com.jfinal.kit.Kv;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
/**数据查询条件校验*/
public class QueryValidate extends AbsValidateInterceptor {
	
	private static Logger log = Logger.getLogger(QueryValidate.class);
	private String driver = PropKit.get("driver");

	@Override
	public Ret verify(HttpServletRequest req) {

		String sqlid = req.getParameter("sqlid");
		if(sqlid!=null){
			Record user = (Record) req.getSession().getAttribute("user");
			String sql1 = "select s.*,l.limits,l.constraint_select from (select * from data_statement_limit where aid=?) l  left join data_statement s on s.id=l.sid  where l.limits like '%SE%' and s.id=? ";
			Record re = Db.findFirst(sql1,user.get("id"),sqlid);
			//QueryDao.findByid("data_statement", Integer.parseInt(sqlid),Kv.by("(limits like '%SE%' or limits like '%EXP%')", null));
			if(re !=null){
				Map<String, String> forms = ServletPro.findParaToMap(req);
				Map<String, String> params = new HashMap<String, String>();

				String wheres = re.getStr("wheres");
				Ret ret = FormValidate.htmlForm(forms, params, wheres,driver);
				if(ret.isFlag()){//校验成功
					StringBuilder sb = new StringBuilder();
					List<Object> values = new ArrayList<Object>();
					int i = 0;
					String constraint_select = re.get("constraint_select");
					sb.append(FormValidate.constraintForm(constraint_select,values));
					
					for(String key:params.keySet()){
						String value = params.get(key);
						if(key.endsWith("%")) {
							sb.append(" and "+key.substring(0, key.length()-1)+" like concat(concat('%',?),'%')");//兼容oracle 和 mysql
							values.add(value);
						}else {
							//时间类型特殊处理
							if(value.indexOf("to_date")==0){
								sb.append(" and "+key+"="+value);				
							}else{								
								sb.append(" and "+key+"=?");
								values.add(value);
							}			
						}
					}
					log.info("sb:"+sb.toString());
				    String sql = re.getStr("sqltext").replace("$(wheres)", sb.toString());
				    if(sb.length()>3){
				    	sql = sql.replace("$(wheres_or)", " or( 1=1 "+sb.toString()+")");
				    }else{
				    	sql = sql.replace("$(wheres_or)", "");
				    }
				    log.info("sql:"+sql);
				    req.setAttribute("sql", sql);
				    req.setAttribute("values", values);
				}
				
				return ret;
			}else{
				return new Ret(false, -3, "权限不足");
			}
		}else{
			return new Ret(false, -4, "无效的表");
		}
		
	}
	
	public static void main(String[] args) {
		String str = "select count(1) from (select aid,latn_name B地市, phone 发展人号码,lxphone 拉新号码,status 发送状态,resdesc 发送结果,qd 渠道,addtime 添加时间 from (select (select latn_name from ahdx_latn where latn_num=t.latnid) latn_name,t.seqid aid, t.phone,t.lxphone,t.rescode,t.status,t.resdesc ,t.qd,t.addtime from tb_ah_yzf_province t order by addtime desc)  where 1!=1  $(wheres_or))";
		str = str.replace("$(wheres_or)", "");
		System.out.println(str);
	}
	
	
	
	@Override
	protected void render(Controller con) {
		super.render(con);
	}
	
	public static void main2(String[] args) {
		String sql = "select * from dual where 1=1 $(wheres) and  $(wheres) and $(wheres)";
		System.out.println(sql.replace("$(wheres)", "a"));
	}

}
